/**
 * Project: topdt-jbpm
 * Copyright 2013  Top Digital Technology  Co. Ltd.
 * All right reserved.
 */
package com.topdt.jbpm.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.thx.common.orm.HibernateBaseDao;
import com.thx.common.util.AppContext;
import com.thx.common.web.Page;
import com.topdt.jbpm.model.HistTodo;
/**
 * 建立日期 : 2013-5-19 下午5:48:30<br>
 * 作者 : balance<br>
 * 模块 : <br>
 * 描述 : <br>
 * 修改历史: 序号 日期 修改人 修改原因 <br>
 * 1 <br>
 * 2 <br>
 */
@Repository
public class HistTodoDao extends HibernateBaseDao<HistTodo> {

	@SuppressWarnings("unchecked")
	public List<HistTodo> getLastTodos(String workId, String actName) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "company_id='" + comId + "' and ";
		String sql = "select * from wf_hist_todo where " + condition + "work_id=? and activity_name=? and "
				+ "batch=(select max(batch) from  wf_hist_todo where work_id=? and activity_name=?) "
				+ " order by seq_num";

		List<HistTodo> list = this.createSqlQuery(sql, workId, actName, workId, actName).addEntity(HistTodo.class)
				.list();
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<HistTodo> findWorkRecordFromHistTodo(String workId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "ht.companyId='" + comId + "' and ";
		String hql = "from HistTodo ht where " + condition + " ht.workId = ? order by ht.arriveTime,ht.batch,ht.finishTime";
		List<HistTodo> list = createQuery(hql, new Object[] { workId }).list();
		return list;
	}

	public void delByWorkId(String workId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "companyId='" + comId + "' and ";

		String hql = "delete from HistTodo where " + condition + " workId=?";
		this.createQuery(hql, workId).executeUpdate();
	}

	/**
	 * 根据work_id删除wf_his_todo表中记录
	 * @author 刘杰
	 * @date   May 16, 2014 3:51:50 PM 
	 * @param delId
	 */
	public void deleteWorks(String delIdsStr) {
		String sql = "delete from wf_hist_todo where work_id   in   ("+delIdsStr+") ";
		createSqlQuery(sql).executeUpdate();
		
		
	}
	/**
	 * @author Wangxufeng
	 * @date 2014-12-04
	 * 分页查询统计所有人的已办histTodo
	 * @param entity
	 * @param page
	 * @return
	 */
	public Page<HistTodo> getHistTodoList(HistTodo entity,Page<HistTodo> page){
		String sql = createSql(entity);
		List<HistTodo> histList = new ArrayList<HistTodo>();
		@SuppressWarnings("unchecked")
		List<Object[]> list = createSqlQuery(sql).setFirstResult(page.getStart()).setMaxResults(page.getMax()).list();
		if(list !=null){
			for(Object[] obj:list){
				HistTodo hist = new HistTodo(); 
				hist.setHandleUserName(String.valueOf(obj[0]));
				hist.setDeptName(String.valueOf(obj[1]));
				hist.setAvgHandleTime(String.valueOf(obj[2]));
				histList.add(hist);
			}
		}
		page.setResult(histList);
		sql = "select count(*) from ("+sql+")";
		int count = 0;
		Object dataSize = createSqlQuery(sql).uniqueResult();
		if(dataSize != null){
			count = Integer.parseInt(dataSize.toString());
		}
		page.setCount(count);
		return page;
	}
	/**
	 * @author Wangxufeng
	 * @date 2014-12-04
	 * 封装查询的sql,平均处理时间单位（分）sql语句如下：
	 * select p.name,p.dept_name,w.AVGTIME from (
	 * select to_user, ceil(sum((FINISH_TIME-ARRIVE_TIME) * 24 * 60)/count(TO_USER)) as avgTime from WF_HIST_TODO 
	 * where to_user in(select TO_USER from WF_HIST_TODO GROUP BY TO_USER) GROUP BY TO_USER
	 *	) w,PERSONNEL p where w.to_user = p.personnel_id
	 * @param entity
	 * @return
	 */
	private String createSql(HistTodo entity) {
		StringBuffer sql = new StringBuffer();
		sql.append("select p.name,p.dept_name,w.AVGTIME from (");
		sql.append("select to_user, ceil(sum((FINISH_TIME-ARRIVE_TIME) * 24 * 60)/count(TO_USER)) as avgTime from WF_HIST_TODO ");
		sql.append("where to_user in(select TO_USER from WF_HIST_TODO where ");
		if(StringUtils.isNotBlank(entity.getPerIds())){
			sql.append("to_user  in (" + entity.getPerIds() + ") ");
		}
		sql.append("GROUP BY TO_USER) GROUP BY TO_USER");
		sql.append(") w,org_user p where w.to_user = p.user_id ");
		if(StringUtils.isNotBlank(entity.getHandleUserName())){
			sql.append("and p.user_name like '%" + entity.getHandleUserName() + "%' ");
		}
		if(StringUtils.isNotBlank(entity.getDeptId())){
			sql.append("and p.dept_id = " + entity.getDeptId());
		}
		sql.append(" order by p.dept_id");
		return sql.toString();
	}
	/**
	 * @author Wangxufeng
	 * @date 2014-12-05
	 * 为导出功能准备的数据
	 * @param entity
	 * @return
	 */
	public List<Map<String, String>> getHistTodoList(HistTodo entity) {
		String sql = createSql(entity);
		List<Map<String, String>> ListMap = new ArrayList<Map<String,String>>();
		@SuppressWarnings("unchecked")
		List<Object[]> list = createSqlQuery(sql).list();
		if(list !=null){
			for(Object[] obj:list){
				Map<String, String> map = new HashMap<String, String>(); 
				map.put("userName", String.valueOf(obj[0]));
				map.put("deptName", String.valueOf(obj[1]));
				map.put("avgHandleTime", String.valueOf(obj[2]));
				ListMap.add(map);
			}
		}
		return ListMap;
	}
}
